﻿using System;
using System.Configuration;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace DataProviderFactory
{
  class Program
  {
    private static string InsertMachineName(string cnStr)
    {
      string compName = Environment.MachineName;
      var pos = cnStr.IndexOf('{');
      var firstPart = cnStr.Substring(0, pos);
      var secondPart = cnStr.Substring(pos + 3);
      return firstPart + compName + secondPart;
    }

    private static void Autolot()
    {
      const string ccc = "Sql";
      Console.WriteLine("*****************************");
      string dp = ConfigurationManager.AppSettings[ccc+"Provider"];
      string cnStr = ConfigurationManager.ConnectionStrings["Autolot"+ccc+"Provider"].ConnectionString;

      string connectionString = InsertMachineName(cnStr);

      DbProviderFactory df = DbProviderFactories.GetFactory(dp);
      using (DbConnection cn = df.CreateConnection())
      {
        Console.WriteLine("Ваш объект подключения: {0}", cn.GetType().Name);
        cn.ConnectionString = connectionString;
        cn.Open();

        DbCommand cmd = df.CreateCommand();
        Console.WriteLine("Ваш объект команды: {0}",cmd.GetType().Name);
        cmd.Connection = cn;
        cmd.CommandText = "select * from Inventory";

        using (DbDataReader dr = cmd.ExecuteReader())
        {
          Console.WriteLine("Ваш объект чтения данных: {0}", dr.GetType().Name);
          Console.WriteLine("*********************************");
          while (dr.Read())
          {
            Console.WriteLine("->Авто № {0} - {1} {2} \"{3}\".",dr["CarId"],dr[2],dr["Make"],dr[3]);
          }
        }
      }
      Console.ReadLine();
    }

    private static void ParadoxKurs()
    {
      Console.WriteLine("*****************************");
      string dp = ConfigurationManager.AppSettings["OleProvider"];
      string cnStr = ConfigurationManager.ConnectionStrings["KursOleProvider"].ConnectionString;

      DbProviderFactory df = DbProviderFactories.GetFactory(dp);
      using (DbConnection cn = df.CreateConnection())
      {
        Console.WriteLine("Ваш объект подключения: {0}", cn.GetType().Name);
        cn.ConnectionString = cnStr;
        cn.Open();

        DbCommand cmd = df.CreateCommand();
        Console.WriteLine("Ваш объект команды: {0}", cmd.GetType().Name);
        cmd.Connection = cn;
        cmd.CommandText = "select * from kurs";

        using (DbDataReader dr = cmd.ExecuteReader())
        {
          Console.WriteLine("Ваш объект чтения данных: {0}", dr.GetType().Name);
          Console.WriteLine("*********************************");
          while (dr.Read())
          {
            Console.WriteLine("Дата: {0:dd/MM/yyyy}  курс {1}", dr[0], dr[1]);
          }
        }
      }
      Console.ReadLine();
    }

    private static void MySqlRuswords()
    {
      Console.WriteLine("*****************************");
      // при создании таблицы через workbench collation задавался utf8 - utf8 general ci
      // в скрипте должно быть
      // DEFAULT CHARACTER SET = utf8
      // COLLATE = utf8_general_ci

      string connectionString = "Database=test; Data Source = 192.168.100.120; User Id = root; Password = root; charset=utf8";
      using (MySqlConnection mySqlConnection = new MySqlConnection(connectionString))
      {
        Console.WriteLine("Ваш объект подключения: {0}", mySqlConnection.GetType().Name);
        mySqlConnection.Open();
        MySqlCommand cmd;

//                string c = "insert into ruswords(id,word) values(2,'апрель')";
//                cmd = new MySqlCommand(c, mySqlConnection);
//                cmd.ExecuteNonQuery();

        string q = "select * from ruswords";
        cmd = new MySqlCommand(q, mySqlConnection);
        using (MySqlDataReader dataReader = cmd.ExecuteReader())
        {
          Console.WriteLine("Ваш объект чтения данных: {0}", dataReader.GetType().Name);
          Console.WriteLine("*********************************");
          while (dataReader.Read())
          {
            Console.WriteLine("id: {0}  word: {1}", dataReader[0], dataReader[1]);
          }
        }
        Console.ReadLine();
      }
    }

    static void Main()
    {
//      Autolot();
//      ParadoxKurs();
      MySqlRuswords();
    }

  }
}
